package com.center.growcenterserver.service.impl;

import com.center.growcenterserver.mapper.GrpMapper;
import com.center.growcenterserver.mapper.StudentCourseMapper;
import com.center.growcenterserver.model.domain.GraduateResearchProcess;
import com.center.growcenterserver.model.domain.StudentCourse;
import com.center.growcenterserver.service.FileUploadService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;


import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;


@Slf4j
@Service
public class FileUploadServiceImpl implements FileUploadService {

    @Autowired
    private StudentCourseMapper StudentCourseMapper;

    @Autowired
    private GrpMapper grpMapper;

    private static final int STUDENT_ID_COLUMN = 0; // 学生ID所在的列索引
    private static final int COURSE_NAME_COLUMN = 1; // 课程名称所在的列索引
    private static final int GRADE_COLUMN = 2;       // 成绩所在的列索引

    /**
     * 解析Excel文件并将每行数据存储在StudentCourse对象中。
     */
    public static List<StudentCourse> parseExcel(String filePath) {
        List<StudentCourse> studentCourses = new ArrayList<>();
        boolean isFirstRow = true; // 用于标识是否是第一行（标题行）

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 假设数据在第一个工作表中
            Sheet sheet = workbook.getSheetAt(0);

            // 遍历行
            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过标题行
                }

                // 初始化一个新的 StudentCourse 对象
                StudentCourse studentCourse = new StudentCourse();

                // 获取学生ID（假设为字符串）
                Cell studentIdCell = row.getCell(STUDENT_ID_COLUMN);
                if (studentIdCell != null && studentIdCell.getCellType() == CellType.STRING) {
                    studentCourse.setStudentId(studentIdCell.getStringCellValue().trim());
                }

                // 获取课程名称（假设为字符串）
                Cell courseNameCell = row.getCell(COURSE_NAME_COLUMN);
                if (courseNameCell != null && courseNameCell.getCellType() == CellType.STRING) {
                    studentCourse.setCourseId(courseNameCell.getStringCellValue().trim());
                }

                // 获取成绩（假设为数值并转换为整数）
                Cell gradeCell = row.getCell(GRADE_COLUMN);
                if (gradeCell != null && gradeCell.getCellType() == CellType.NUMERIC) {
                    studentCourse.setGrade(BigDecimal.valueOf((int) gradeCell.getNumericCellValue()));
                }

                // 将填充好的 StudentCourse 对象添加到列表中
                studentCourses.add(studentCourse);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return studentCourses;
    }

    // 定义列索引常量
    private static final int STEP_COLUMN = 0;
    private static final int DESCRIPTION_COLUMN = 1;
    private static final int TIMELINE_COLUMN = 2;
    private static final int CONDITION_COLUMN = 3;

    public static List<GraduateResearchProcess> parseExcel2(String filePath) {
        List<GraduateResearchProcess> processes = new ArrayList<>();
        boolean isFirstRow = true; // 用于标识是否是第一行（标题行）

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过标题行
                }

                GraduateResearchProcess process = new GraduateResearchProcess();

                Cell stepCell = row.getCell(STEP_COLUMN);
                if (stepCell != null && stepCell.getCellType() == CellType.STRING) {
                    process.setStep(stepCell.getStringCellValue().trim());
                }

                Cell descCell = row.getCell(DESCRIPTION_COLUMN);
                if (descCell != null && descCell.getCellType() == CellType.STRING) {
                    process.setDescription(descCell.getStringCellValue().trim());
                }

                Cell timelineCell = row.getCell(TIMELINE_COLUMN);
                if (timelineCell != null) {
                    switch (timelineCell.getCellType()) {
                        case STRING:
                            process.setTimeline(timelineCell.getStringCellValue().trim());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(timelineCell)) {
                                process.setTimeline(timelineCell.getDateCellValue().toString());
                            } else {
                                process.setTimeline(Double.toString(timelineCell.getNumericCellValue()));
                            }
                            break;
                        default:
                            process.setTimeline("");
                    }
                }

                Cell conditionCell = row.getCell(CONDITION_COLUMN);
                if (conditionCell != null && conditionCell.getCellType() == CellType.STRING) {
                    process.setCondi(conditionCell.getStringCellValue().trim());
                }

                processes.add(process);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        return processes;
    }

    /**
     * 插入学生课程记录到数据库。
     */
    private int insertStudentCourses(List<StudentCourse> studentCourses) {
        int count = 0;
        for (StudentCourse studentCourse : studentCourses) {
            try {
                StudentCourseMapper.insert(studentCourse);
                count++;
                System.out.println("insert successfully");
            } catch (Exception e) {
                log.error("插入记录时出错: " + studentCourse, e);
            }
        }
        return count;
    }


    @Override
    public String saveFileData(String filePath) throws Exception {
        String excelFilePath = filePath; // 替换为您的Excel文件路径
        List<StudentCourse> studentCourses = parseExcel(excelFilePath);
        // 插入所有记录到数据库
        int insertedCount = insertStudentCourses(studentCourses);

        return "成功上传并插入了 " + insertedCount + " 条记录";

    }

    @Override
    public String saveFileData2(String filePath) throws Exception {
        String excelFilePath = filePath; // 替换为您的Excel文件路径
        List<GraduateResearchProcess> graduateResearchProcesses = parseExcel2(excelFilePath);
        // 插入所有记录到数据库
        int insertedCount = insertgrp(graduateResearchProcesses);

        return "成功上传并插入了 " + insertedCount + " 条记录";
    }

    private int insertgrp(List<GraduateResearchProcess> graduateResearchProcesses) {
        int count = 0;
        for (GraduateResearchProcess graduateResearchProcess : graduateResearchProcesses) {
            try {
                grpMapper.insert(graduateResearchProcess);
                count++;
                System.out.println("insert successfully");
            } catch (Exception e) {
                log.error("插入记录时出错: " + graduateResearchProcesses, e);
            }
        }
        return count;
    }

}
